In today's technological environment, it is common for enterprises such as business organizations, schools, charitable organizations and government organizations to deploy private computer networks—intranets—to securely share such organization's information or network operating system within that organization. The term “intranet” is used in contrast to “internet”, which is a network between and among individuals and organizations, the most common of which is the global, public Internet. That is, an intranet is a network within an enterprise which sometimes refers only to an internal (private) website, but may be a more extensive part of the enterprise's information technology (IT) infrastructure. For example, in many enterprises, an employee may work from home and gain access to the enterprise via a portal requiring additional security hurdles such as synchronous keys for entry. An enterprise intranet may host multiple private websites and constitute an important component and focal point of internal communication with, and collaboration among, individual computer users associated/affiliated with the enterprise (e.g., students within a university, co-workers within a company, members of a governmental department or the like).
As individual computer users associated/affiliated with the enterprise perform various computer-based activities while logged into the intranet, these users may utilize enterprise resource planning (ERP) client/server systems as provided by the enterprise. One such system known as the Dynamics® AX 2009 ERP system available from Microsoft Corporation of Redmond, Wash. provides a database query server and associated application object server among other features. These are routinely accessed by a client, for example, to query the database for data contained in tables. The database may comprise a plurality of related tables of data, for example, related to everyday activities of the enterprise. A member of the enterprise may perform a query of the database known as a join query in which, for example, in the sequential query language (SQL), a join query relates to a query in which two (or more) tables of data of a database are temporarily joined in order to select data of the tables without impacting the data in the tables. To perform a join query, information in the query may comprise at least one table, a column or row and a condition to make the join temporarily occur.
In known enterprise systems, a join query may be processed at the database each time the join is executed. This can cause degradation in performance of the ERP system as a whole. Even if the same query is run several times, the same query may be executed on the database each time the query is run. In the case of a typical enterprise structure involving a client (making a query), a server and a database, making the same query may lead to expensive network calls, time and resource consumption impacting the ERP system as a whole.
In at least one enterprise resource planning system, data caching is performed for a unique single table select query, meaning when there is no join condition or table inheritance to connect a first database table to another. There exists a unique index of a table of the database. However, the query is not a join query neither is there a join condition or inheritance connection to another table. Referring to FIGS. 2A, 2B, 4 and 5, there is shown a process of data caching at a level of a data record of a table having a unique index of a database which may be referred to herein as record level caching. Similar reference characters are used to denote similar elements in similar drawings. Also, the first numeral of a reference may indicate the figure number where that element first appears.
Unique keys may be declared using unique indexes. A SELECT statement may select a record from a table using an equal operator, for example, in one language, denoted == on the caching key. Referring to FIGS. 2A and 2B, there are shown a plurality of exemplary data structures involved in record level caching. The names of the data structures are exemplary names and not intended to be limiting. Beginning at the left of FIG. 2A, TableInfo 210 contains a reference to TableDataBaseInfo 215 which refers to RecordCacheGroup 220. RecordCacheGroup 220 in turn refers to one RecordCacheInfo 225 per the unique index for the desired table. RecordCacheInfo corresponds to one unique index definition, for example, DataBaseIndexDefinition. A root of the RecordCacheInfo 225 may be represented as header AVLTree 240. Referring to FIG. 2B, AVLCache may refer to DataBaseRecordBufferRefCounted 245 which maintains a reference to DataBaseRecordBuffer 250.
In this process, data structure DataBaseRecordStructure 250 may be a data structure that may comprise the actual data. The data may be stored in a byte array of dataRow. DataRow is used by way of example and may comprise a data column in an alternative embodiment. The data represented by an instance of DataBaseRecordBuffer 250 correlates to a row of data in the database table, for example, a table of a database of database server 118 (FIG. 1). RecordCacheInfo 225 may be an associative container of DataBaseRecordBuffer 250. The RecordCacheInfo structure 225 may be an associative container of DataBaseRecordBuffer 250. The RecordCacheInfo structure may represent a unique index in the database. An associative container may be built on an AVL tree 240 (a self-balancing binary search tree named for Adeison-Velski/Landis). FIG. 4 represents record level caching in action according to one process of unique table caching and will not be described in detail herein.
Data retrieval from record level caching is exemplified by FIG. 2B. A where clause in the database query may match any of the unique indexes on the table. The data row in a record level cache, for example, may be represented internally in the kernel 625 (FIG. 6) of an application object server 116, 600 using DataBaseRecordBuffer 250. In a record level cache, DatabaseRecordBuffer 250 may be stored as a node in an AVL tree 240. Each node of an AVL tree 240 may be represented using AVLTREEHeader structure 240 (FIG. 2A). Since there may exist a collection of unique indexes, the kernel 625 may use a reference counting scheme instead of making copies of a data buffer for each index to keep track of the DataBaseRecordBuffer 250 structures that are stored in the AVLTREEHeader 240. For example, the DataBaseRecordBufferRefCounted 245 structure may be a wrapper for DatabaseRecordBuffer 245 with a reference count. If the record is not found in the cache, then the record may be fetched from the database and inserted into each of the trees corresponding to unique indexes.
Fundamentally, there is a need in the art for a caching of join queries to avoid the degradation in performance so that the caching may result in more efficient ERP system operation. Moreover, a problem may arise during normalization of an enterprise resource planning system when a number of single table select queries may be replaced by join queries. Also, table inheritance, when one table may be the parent of another table and so on, may cause repetitive queries of the same tables and the same join query processes may need to be performed.
Thus, it may be known to provide a unique single table select query, however, what are needed are systems, methods and computer program products for adapting existing processes for unique single table select caching to expand caching to, for example, unique join and table inheritance object queries.